package com.swe.chums.dao.impl;

import com.swe.chums.dao.WebResourceDao;
import com.swe.chums.dao.model.WebResource;
import com.swe.chums.dao.model.WebResourcePk;
import com.swe.chums.dao.exceptions.WebResourceDaoException;
import java.util.List;
import javax.sql.DataSource;
import java.sql.ResultSet;
import java.sql.SQLException;
import org.springframework.jdbc.core.simple.SimpleJdbcTemplate;
import org.springframework.jdbc.core.simple.ParameterizedRowMapper;
import org.springframework.transaction.annotation.Transactional;

public class WebResourceDaoImpl extends AbstractDAO implements ParameterizedRowMapper<WebResource>, WebResourceDao
{
	protected SimpleJdbcTemplate jdbcTemplate;

	protected DataSource dataSource;

	/**
	 * Method 'setDataSource'
	 * 
	 * @param dataSource
	 */
	public void setDataSource(DataSource dataSource)
	{
		this.dataSource = dataSource;
		jdbcTemplate = new SimpleJdbcTemplate(dataSource);
	}

	/**
	 * Method 'insert'
	 * 
	 * @param dto
	 * @return WebResourcePk
	 */
	@Transactional
	public WebResourcePk insert(WebResource dto)
	{
		jdbcTemplate.update("INSERT INTO " + getTableName() + " ( web_resource_type_id, url_hash_id, url, title, description, thumbnail_file_name, average_rating, insert_date, hit_count) VALUES ( ?, ?, ?, ?, ?, ?, ?, ?, ? )",dto.getWebResourceTypeId(),dto.isUrlHashIdNull()?null:dto.getUrlHashId(),dto.getUrl(),dto.getTitle(),dto.getDescription(),dto.getThumbnailFileName(),dto.getAverageRating(), dto.getInsertDate(), dto.getHitCount());
		WebResourcePk pk = new WebResourcePk();
		pk.setId( jdbcTemplate.queryForInt("select last_insert_id()") );
		return pk;
	}

	/** 
	 * Updates a single row in the web_resource table.
	 */
	@Transactional
	public void update(WebResourcePk pk, WebResource dto) throws WebResourceDaoException
	{
		jdbcTemplate.update("UPDATE " + getTableName() + " SET id = ?, web_resource_type_id = ?, url_hash_id = ?, url = ?, title = ?, description = ?, thumbnail_file_name = ?, average_rating = ?, insert_date=?, hit_count=? WHERE id = ?",dto.getId(),dto.getWebResourceTypeId(),dto.getUrlHashId(),dto.getUrl(),dto.getTitle(),dto.getDescription(),dto.getThumbnailFileName(),dto.getAverageRating(),dto.getInsertDate(),dto.getHitCount(),pk.getId());
	}

	/** 
	 * Deletes a single row in the web_resource table.
	 */
	@Transactional
	public void delete(WebResourcePk pk) throws WebResourceDaoException
	{
		jdbcTemplate.update("DELETE FROM " + getTableName() + " WHERE id = ?",pk.getId());
	}

	/**
	 * Method 'mapRow'
	 * 
	 * @param rs
	 * @param row
	 * @throws SQLException
	 * @return WebResource
	 */
	public WebResource mapRow(ResultSet rs, int row) throws SQLException
	{
		WebResource dto = new WebResource();
		dto.setId( rs.getInt( 1 ) );
		dto.setWebResourceTypeId( rs.getInt( 2 ) );
		dto.setUrlHashId( rs.getInt( 3 ) );
		if (rs.wasNull()) {
			dto.setUrlHashIdNull( true );
		}
		
		dto.setUrl( rs.getString( 4 ) );
		dto.setTitle( rs.getString( 5 ) );
		dto.setDescription( rs.getString( 6 ) );
		dto.setThumbnailFileName( rs.getString( 7 ) );
		dto.setAverageRating( rs.getDouble( 8 ) );
                dto.setInsertDate(rs.getTimestamp( 9 ) );
                dto.setHitCount(rs.getInt( 10 ) );
		return dto;
	}

	/**
	 * Method 'getTableName'
	 * 
	 * @return String
	 */
	public String getTableName()
	{
		return "spring2013g3.web_resource";
	}

	/** 
	 * Returns all rows from the web_resource table that match the criteria 'id = :id'.
	 */
	@Transactional
	public WebResource findByPrimaryKey(int id) throws WebResourceDaoException
	{
		try {
			List<WebResource> list = jdbcTemplate.query("SELECT id, web_resource_type_id, url_hash_id, url, title, description, thumbnail_file_name, average_rating, insert_date, hit_count FROM " + getTableName() + " WHERE id = ?", this,id);
			return list.size() == 0 ? null : list.get(0);
		}
		catch (Exception e) {
			throw new WebResourceDaoException("Query failed", e);
		}
		
	}

	/** 
	 * Returns all rows from the web_resource table that match the criteria ''.
	 */
	@Transactional
	public List<WebResource> findAll() throws WebResourceDaoException
	{
		try {
			return jdbcTemplate.query("SELECT id, web_resource_type_id, url_hash_id, url, title, description, thumbnail_file_name, average_rating, insert_date, hit_count FROM " + getTableName() + " ORDER BY id", this);
		}
		catch (Exception e) {
			throw new WebResourceDaoException("Query failed", e);
		}
		
	}

	/** 
	 * Returns all rows from the web_resource table that match the criteria 'web_resource_type_id = :webResourceTypeId'.
	 */
	@Transactional
	public List<WebResource> findByWebResourceType(int webResourceTypeId) throws WebResourceDaoException
	{
		try {
			return jdbcTemplate.query("SELECT id, web_resource_type_id, url_hash_id, url, title, description, thumbnail_file_name, average_rating, insert_date, hit_count FROM " + getTableName() + " WHERE web_resource_type_id = ?", this,webResourceTypeId);
		}
		catch (Exception e) {
			throw new WebResourceDaoException("Query failed", e);
		}
		
	}

	/** 
	 * Returns all rows from the web_resource table that match the criteria 'id = :id'.
	 */
	@Transactional
	public List<WebResource> findWhereIdEquals(int id) throws WebResourceDaoException
	{
		try {
			return jdbcTemplate.query("SELECT id, web_resource_type_id, url_hash_id, url, title, description, thumbnail_file_name, average_rating, insert_date, hit_count FROM " + getTableName() + " WHERE id = ? ORDER BY id", this,id);
		}
		catch (Exception e) {
			throw new WebResourceDaoException("Query failed", e);
		}
		
	}

	/** 
	 * Returns all rows from the web_resource table that match the criteria 'web_resource_type_id = :webResourceTypeId'.
	 */
	@Transactional
	public List<WebResource> findWhereWebResourceTypeIdEquals(int webResourceTypeId) throws WebResourceDaoException
	{
		try {
			return jdbcTemplate.query("SELECT id, web_resource_type_id, url_hash_id, url, title, description, thumbnail_file_name, average_rating, insert_date, hit_count FROM " + getTableName() + " WHERE web_resource_type_id = ? ORDER BY web_resource_type_id", this,webResourceTypeId);
		}
		catch (Exception e) {
			throw new WebResourceDaoException("Query failed", e);
		}
		
	}

	/** 
	 * Returns all rows from the web_resource table that match the criteria 'url_hash_id = :urlHashId'.
	 */
	@Transactional
	public List<WebResource> findWhereUrlHashIdEquals(int urlHashId) throws WebResourceDaoException
	{
		try {
			return jdbcTemplate.query("SELECT id, web_resource_type_id, url_hash_id, url, title, description, thumbnail_file_name, average_rating, insert_date, hit_count FROM " + getTableName() + " WHERE url_hash_id = ? ORDER BY url_hash_id", this,urlHashId);
		}
		catch (Exception e) {
			throw new WebResourceDaoException("Query failed", e);
		}
		
	}

	/** 
	 * Returns all rows from the web_resource table that match the criteria 'url = :url'.
	 */
	@Transactional
	public List<WebResource> findWhereUrlEquals(String url) throws WebResourceDaoException
	{
		try {
			return jdbcTemplate.query("SELECT id, web_resource_type_id, url_hash_id, url, title, description, thumbnail_file_name, average_rating, insert_date, hit_count FROM " + getTableName() + " WHERE url = ? ORDER BY url", this,url);
		}
		catch (Exception e) {
			throw new WebResourceDaoException("Query failed", e);
		}
		
	}

	/** 
	 * Returns all rows from the web_resource table that match the criteria 'title = :title'.
	 */
	@Transactional
	public List<WebResource> findWhereTitleEquals(String title) throws WebResourceDaoException
	{
		try {
			return jdbcTemplate.query("SELECT id, web_resource_type_id, url_hash_id, url, title, description, thumbnail_file_name, average_rating, insert_date, hit_count FROM " + getTableName() + " WHERE title = ? ORDER BY title", this,title);
		}
		catch (Exception e) {
			throw new WebResourceDaoException("Query failed", e);
		}
		
	}

	/** 
	 * Returns all rows from the web_resource table that match the criteria 'description = :description'.
	 */
	@Transactional
	public List<WebResource> findWhereDescriptionEquals(String description) throws WebResourceDaoException
	{
		try {
			return jdbcTemplate.query("SELECT id, web_resource_type_id, url_hash_id, url, title, description, thumbnail_file_name, average_rating, insert_date, hit_count FROM " + getTableName() + " WHERE description = ? ORDER BY description", this,description);
		}
		catch (Exception e) {
			throw new WebResourceDaoException("Query failed", e);
		}
		
	}

	/** 
	 * Returns all rows from the web_resource table that match the criteria 'thumbnail_file_name = :thumbnailFileName'.
	 */
	@Transactional
	public List<WebResource> findWhereThumbnailFileNameEquals(String thumbnailFileName) throws WebResourceDaoException
	{
		try {
			return jdbcTemplate.query("SELECT id, web_resource_type_id, url_hash_id, url, title, description, thumbnail_file_name, average_rating, insert_date, hit_count FROM " + getTableName() + " WHERE thumbnail_file_name = ? ORDER BY thumbnail_file_name", this,thumbnailFileName);
		}
		catch (Exception e) {
			throw new WebResourceDaoException("Query failed", e);
		}
		
	}

	/** 
	 * Returns all rows from the web_resource table that match the criteria 'average_rating = :averageRating'.
	 */
	@Transactional
	public List<WebResource> findWhereAverageRatingEquals(String averageRating) throws WebResourceDaoException
	{
		try {
			return jdbcTemplate.query("SELECT id, web_resource_type_id, url_hash_id, url, title, description, thumbnail_file_name, average_rating, insert_date, hit_count FROM " + getTableName() + " WHERE average_rating = ? ORDER BY average_rating", this,averageRating);
		}
		catch (Exception e) {
			throw new WebResourceDaoException("Query failed", e);
		}
		
	}

	/** 
	 * Returns the rows from the web_resource table that matches the specified primary-key value.
	 */
	public WebResource findByPrimaryKey(WebResourcePk pk) throws WebResourceDaoException
	{
		return findByPrimaryKey( pk.getId() );
	}

        @Override
        public List<WebResource> findLatest(int limit, int webResourceType) throws WebResourceDaoException {
 		try {
			return jdbcTemplate.query("SELECT id, web_resource_type_id, url_hash_id, url, title, description, thumbnail_file_name, average_rating, insert_date, hit_count FROM " + getTableName() + " WHERE web_resource_type_id = ?  ORDER BY insert_date desc LIMIT ?", this, webResourceType, limit);
		}
		catch (Exception e) {
			throw new WebResourceDaoException("Query failed", e);
		}
        }

        @Override
        public List<WebResource> findTop(int limit, int webResourceType) throws WebResourceDaoException {
                try {
			return jdbcTemplate.query("SELECT id, web_resource_type_id, url_hash_id, url, title, description, thumbnail_file_name, average_rating, insert_date, hit_count FROM " + getTableName() + " WHERE web_resource_type_id = ? and title<>''  ORDER BY average_rating desc LIMIT ?", this, webResourceType, limit);
		}
		catch (Exception e) {
			throw new WebResourceDaoException("Query failed", e);
		}
        }

    @Override
    public List<WebResource> getTrendingWebResources(int limit, int webResourceType) throws WebResourceDaoException {
                try {
			return jdbcTemplate.query("SELECT id, web_resource_type_id, url_hash_id, url, title, description, thumbnail_file_name, average_rating, insert_date, hit_count FROM " + getTableName() + " WHERE web_resource_type_id = ? and title<>''  ORDER BY hit_count desc LIMIT ?", this, webResourceType, limit);
		}
		catch (Exception e) {
			throw new WebResourceDaoException("Query failed", e);
		}
    }

    @Override
    public List<WebResource> getRecommendedWebResources(String inStatement) throws WebResourceDaoException {
                try {
			return jdbcTemplate.query("SELECT id, web_resource_type_id, url_hash_id, url, title, description, thumbnail_file_name, average_rating, insert_date, hit_count FROM " + getTableName() + " WHERE web_resource_type_id=1 and id in (select distinct web_resource_id from tag where tag_type_id in "+ inStatement+" )", this);
		}
		catch (Exception e) {
			throw new WebResourceDaoException("Query failed", e);
		}
            
    }

}
